﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using InfoLayer;

namespace DataAccessLayer
{
    public class PhieuThuePhongDataAccess:DataAccessAbstract
    {
        private OleDbConnection _cnn;
        private OleDbDataAdapter _da;
        private OleDbCommand _cmd;
        private OleDbDataReader _dr;
        private String _sqlString;

        public ArrayList LayDanhSach()
        {
            ArrayList kq = new ArrayList();
            PhieuThuePhongInfo Info;
            _sqlString = "Select * from PHIEUTHUEPHONG";
            //khoi tao bien ket noi CSDL
            _cnn = ConnectionData();
            //chuoi ket noi CSDL
            _cmd = new OleDbCommand(_sqlString, _cnn);
            _dr = _cmd.ExecuteReader();
            //doc du lieu tu CSDL
            while (_dr.Read())
            {
                Info = new PhieuThuePhongInfo();
                try
                {
                    //Info.TinhTrangPhong = Convert.ToBoolean(_dr["TinhTrangPhong"].ToString());
                }
                catch (System.Exception ex)
                {
                    _cnn.Close();
                    throw new Exception("Lỗi kết nối dữ liệu.");
                }
                kq.Add(Info);
            }
            _cnn.Close();
            return kq;
        }

        public DataTable LayBangDanhSachPhieuThuePhong()
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from PHIEUTHUEPHONG";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public void Insert(ref PhieuThuePhongInfo phieuThuePhongInfo)
        {
            //viet cau lenh SQL
            _sqlString = "INSERT INTO PHIEUTHUEPHONG (MaPhieuThue, MaPhong, NgayThuePhong, DonGiaThue, SoNgayThue, ThanhTien, MaHoaDon) VALUES ('" +
                phieuThuePhongInfo.MaPhieuThue + "','" +
                phieuThuePhongInfo.MaPhong + "','" +
                phieuThuePhongInfo.NgayThuePhong + "'," +
                phieuThuePhongInfo.DonGiaThue + "," +
                phieuThuePhongInfo.SoNgayThue + "," +
                phieuThuePhongInfo.ThanhTien + ",'" +
                phieuThuePhongInfo.MaHoaDon + "')";
            //ket noi CSDL
            _cnn = ConnectionData();
            try
            {
                //thu thi cau truy van SQL
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi kết nối cơ sở dữ liệu.");
            }
            _cnn.Close();
        }

        public void Update(PhieuThuePhongInfo phieuThuePhongInfo)
        {
            _sqlString = "UPDATE PHIEUTHUEPHONG SET " +
                                "MaPhong = '" + phieuThuePhongInfo.MaPhong + "'," +
                                "NgayThuePhong = '" + phieuThuePhongInfo.NgayThuePhong + "'," +
                                "DonGiaThue = " + phieuThuePhongInfo.DonGiaThue + "," +
                                "SoNgayThue = " + phieuThuePhongInfo.SoNgayThue + "," +
                                "ThanhTien = " + phieuThuePhongInfo.ThanhTien + "," +
                                "MaHoaDon = '" + phieuThuePhongInfo.MaHoaDon + "'" +
                                "WHERE MaPhieuThue = '" + phieuThuePhongInfo.MaPhieuThue + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi Update dữ liệu.");
            }
            _cnn.Close();
        }

        public void Delete(PhieuThuePhongInfo phieuThuePhongInfo)
        {
            _sqlString = "delete from PHIEUTHUEPHONG where MaPhieuThue = '" + phieuThuePhongInfo.MaPhieuThue + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi xóa dữ liệu.");
            }
            _cnn.Close();
        }

        public string TaoMaTuDong(string maGoc, int batDau)
        {
            string str, kq = "";
            int so;
            try
            {
                str = maGoc.Substring(batDau);
                kq = maGoc.Substring(0, batDau);
                so = Convert.ToInt32(str) + 1;
                for (int i = 0; i < str.Length - so.ToString().Length; i++)
                    kq += "0";
                kq = kq + so.ToString();
                return kq;
            }
            catch //(System.Exception ex)
            {
                return null;
            }
        }

        public DataTable LayDonGia(String _MaPhong)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select DonGiaPhong from LOAIPHONG, PHONG where (LOAIPHONG.MaLoaiPhong = PHONG.MaLoaiPhong) and (PHONG.MaPhong = '" + _MaPhong + "')";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable LayGiaTri(String _MaPhong)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select DonGiaThue from PHIEUTHUEPHONG where PHIEUTHUEPHONG.MaPhong = '" + _MaPhong + "'";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable LayDSPhieu_ChuaTT(String _MaPhong)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select MaPhieuThue, NgayThuePhong, DonGiaThue, MaPhong from PHIEUTHUEPHONG " +
                           "where PHIEUTHUEPHONG.SoNgayThue = " + 0 + " and PHIEUTHUEPHONG.MaPhong = '" + _MaPhong + "'";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public void Update_DaTT(PhieuThuePhongInfo phieuThuePhongInfo)
        {
            _sqlString = "UPDATE PHIEUTHUEPHONG SET " +
                                //"MaPhong = '" + phieuThuePhongInfo.MaPhong + "'," +
                                //"NgayThuePhong = '" + phieuThuePhongInfo.NgayThuePhong + "'," +
                                //"DonGiaThue = " + phieuThuePhongInfo.DonGiaThue + "," +
                                "SoNgayThue = " + phieuThuePhongInfo.SoNgayThue + "," +
                                "ThanhTien = " + phieuThuePhongInfo.ThanhTien + "," +
                                "MaHoaDon = '" + phieuThuePhongInfo.MaHoaDon + "'" +
                                "WHERE MaPhieuThue = '" + phieuThuePhongInfo.MaPhieuThue + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi Update dữ liệu.");
            }
            _cnn.Close();
        }

        public DataTable LaySoLuongKhach(String _MaPhieuThue)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select count(*) from CHITIETPHIEUTHUE, PHIEUTHUEPHONG " +
                           "where CHITIETPHIEUTHUE.MaPhieuThue = PHIEUTHUEPHONG.MaPhieuThue and PHIEUTHUEPHONG.MaPhieuThue = '" + _MaPhieuThue + "'";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable LayLoaiKhach(String _MaPhieuThue)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select LOAIKHACHHANG.MaLoaiKhach, TenLoaiKhach, HesoLoaiKhach from LOAIKHACHHANG, KHACHHANG, CHITIETPHIEUTHUE, PHIEUTHUEPHONG " +
                           "where LOAIKHACHHANG.MaLoaiKhach = KHACHHANG.MaLoaiKhach and KHACHHANG.MaKhachHang = CHITIETPHIEUTHUE.MaKhachHang " +
                            "and CHITIETPHIEUTHUE.MaPhieuThue = PHIEUTHUEPHONG.MaPhieuThue and PHIEUTHUEPHONG.MaPhieuThue = '" + _MaPhieuThue + "'";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable LayBangDanhSachThanhTien()
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from LOAIPHONG order by DonGiaPhong ASC";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable Timkiemphong(String tungay, String denngay)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select distinct (PHONG.MaPhong), PHONG.TenPhong, PHONG.MaLoaiPhong, PHONG.TinhTrangPhong, PHONG.GhiChu " +
                "  from PHIEUTHUEPHONG, PHONG where ";
            if (tungay != null)
            {
                _sqlString += " PHIEUTHUEPHONG.NgayThuePhong >= #" + tungay + "#" +
                    "  and PHIEUTHUEPHONG.NgayThuePhong <= #" + denngay + "# " +
                    "  and PHONG.MaPhong = PHIEUTHUEPHONG.MaPhong ";
                   
            }
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable TimKiemTriGia(int donGiaTu, int donGiaDen)
        {
            DataTable DSPHONG = new DataTable();
            string ngay_hientai = DateTime.Now.ToShortDateString();
            _cnn = ConnectionData();
            _sqlString = "Select distinct PHONG.MaPhong, PHONG.TenPhong, PHONG.MaLoaiPhong, PHONG.TinhTrangPhong, PHONG.GhiChu" +
                "  from PHIEUTHUEPHONG, PHONG where ";
            if (donGiaTu >= 0 && donGiaDen >= 0)
            {
                _sqlString += " PHIEUTHUEPHONG.DonGiaThue >= " + donGiaTu + " and PHIEUTHUEPHONG.DonGiaThue <=  "
                    + donGiaDen + " and PHONG.MaPhong = PHIEUTHUEPHONG.MaPhong";
            }
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

    }
}
